Date/Time Functions and Operators¶
Contents
- Date/Time Functions and Operators
- ADDDATE, DATE_ADD
- ADDTIME
- ADD_MONTHS
- CURDATE, CURRENT_DATE
- CURRENT_DATETIME, NOW
- CURTIME, CURRENT_TIME
- CURRENT_TIMESTAMP, LOCALTIME, LOCALTIMESTAMP
- DATE
- DATEDIFF
- DATE_SUB, SUBDATE
- DAY, DAYOFMONTH
- DAYOFWEEK
- DAYOFYEAR
- EXTRACT
- FROM_DAYS
- FROM_TZ
- FROM_UNIXTIME
- HOUR
- LAST_DAY
- MAKEDATE
- MAKETIME
- MINUTE
- MONTH
- MONTHS_BETWEEN
- NEW_TIME
- QUARTER
- ROUND
- SEC_TO_TIME
- SECOND
- SYS_DATE, SYSDATE
- SYS_DATETIME, SYSDATETIME
- SYS_TIME, SYSTIME
- SYS_TIMESTAMP, SYSTIMESTAMP
- TIME
- TIME_TO_SEC
- TIMEDIFF
- TIMESTAMP
- TO_DAYS
- TRUNC
- TZ_OFFSET
- UNIX_TIMESTAMP
- UTC_DATE
- UTC_TIME
- WEEK
- WEEKDAY
- YEAR
ADDDATE, DATE_ADD¶
-
ADDDATE
(date, INTERVAL expr unit)¶
-
ADDDATE
(date, days)
-
DATE_ADD
(date, INTERVAL expr unit)¶ The ADDDATE function performs an addition or subtraction operation on a specific DATE value; ADDDATE and DATE_ADD are used interchangeably. The return value is a DATE or DATETIME type. The DATETIME type is returned in the following cases.
- The first argument is a DATETIME or TIMESTAMP type
- The first argument is a DATE type and the unit of INTERVAL value specified is less than the unit of day
Therefore, to return value of DATETIME type, you should convert the value of first argument by using the CAST function. Even though the date resulting from the operation exceeds the last day of the month, the function returns a valid DATE value considering the last date of the month.
If every input argument value of date and time is 0, the return value is determined by the return_null_on_function_errors system parameter; if it is set to yes, then NULL is returned; if it is set to no, an error is returned. The default value is no.
If the calculated value is between '0000-00-00 00:00:00' and '0001-01-01 00:00:00', a value having 0 for all arguments is returned in DATE or DATETIME type. Note that operation in JDBC program is determined by the configuration of zeroDateTimeBehavior, connection URL property. For more information about JDBC connection URL, please see Configuration Connection.
Parameters: - date -- It is a DATE, TIMETIME, or TIMESTAMP expression that represents the start date. If an invalid DATE value such as '2006-07-00' is specified, an error is returned.
- expr -- It represents the interval value to be added to the start date. If a negative number is specified next to the INTERVAL keyword, the interval value is subtracted from the start date.
- unit -- It represents the unit of the interval value specified in the expr expression. See the following table to specify the format for the interpretation of the interval value. If the value of expr unit is less than the number requested in the unit, it is specified from the smallest unit. For example, if it is HOUR_SECOND, three values such as 'HOURS:MINUTES:SECONDS' are required. In the case, if only two values such as "1:1" are given, it is regarded as 'MINUTES:SECONDS'.
Return type: DATE or DATETIME
Format of expr for unit
unit Value | expr Format | Example |
---|---|---|
MILLISECOND | MILLISECONDS | ADDDATE(SYSDATE, INTERVAL 123 MILLISECOND) |
SECOND | SECONDS | ADDDATE(SYSDATE, INTERVAL 123 SECOND) |
MINUTE | MINUTES | ADDDATE(SYSDATE, INTERVAL 123 MINUTE) |
HOUR | HOURS | ADDDATE(SYSDATE, INTERVAL 123 HOUR) |
DAY | DAYS | ADDDATE(SYSDATE, INTERVAL 123 DAY) |
WEEK | WEEKS | ADDDATE(SYSDATE, INTERVAL 123 WEEK) |
MONTH | MONTHS | ADDDATE(SYSDATE, INTERVAL 12 MONTH) |
QUARTER | QUARTERS | ADDDATE(SYSDATE, INTERVAL 12 QUARTER) |
YEAR | YEARS | ADDDATE(SYSDATE, INTERVAL 12 YEAR) |
SECOND_MILLISECOND | 'SECONDS.MILLISECONDS' | ADDDATE(SYSDATE, INTERVAL '12.123' SECOND_MILLISECOND) |
MINUTE_MILLISECOND | 'MINUTES:SECONDS.MILLISECONDS' | ADDDATE(SYSDATE, INTERVAL '12:12.123' MINUTE_MILLISECOND) |
MINUTE_SECOND | 'MINUTES:SECONDS' | ADDDATE(SYSDATE, INTERVAL '12:12' MINUTE_SECOND) |
HOUR_MILLISECOND | 'HOURS:MINUTES:SECONDS.MILLISECONDS' | ADDDATE(SYSDATE, INTERVAL '12:12:12.123' HOUR_MILLISECOND) |
HOUR_SECOND | 'HOURS:MINUTES:SECONDS' | ADDDATE(SYSDATE, INTERVAL '12:12:12' HOUR_SECOND) |
HOUR_MINUTE | 'HOURS:MINUTES' | ADDDATE(SYSDATE, INTERVAL '12:12' HOUR_MINUTE) |
DAY_MILLISECOND | 'DAYS HOURS:MINUTES:SECONDS.MILLISECONDS' | ADDDATE(SYSDATE, INTERVAL '12 12:12:12.123' DAY_MILLISECOND) |
DAY_SECOND | 'DAYS HOURS:MINUTES:SECONDS' | ADDDATE(SYSDATE, INTERVAL '12 12:12:12' DAY_SECOND) |
DAY_MINUTE | 'DAYS HOURS:MINUTES' | ADDDATE(SYSDATE, INTERVAL '12 12:12' DAY_MINUTE) |
DAY_HOUR | 'DAYS HOURS' | ADDDATE(SYSDATE, INTERVAL '12 12' DAY_HOUR) |
YEAR_MONTH | 'YEARS-MONTHS' | ADDDATE(SYSDATE, INTERVAL '12-13' YEAR_MONTH) |
SELECT SYSDATE, ADDDATE(SYSDATE,INTERVAL 24 HOUR), ADDDATE(SYSDATE, 1);
03/30/2010 12:00:00.000 AM 03/31/2010 03/31/2010
--it subtracts days when argument < 0
SELECT SYSDATE, ADDDATE(SYSDATE,INTERVAL -24 HOUR), ADDDATE(SYSDATE, -1);
03/30/2010 12:00:00.000 AM 03/29/2010 03/29/2010
--when expr is not fully specified for unit
SELECT SYS_DATETIME, ADDDATE(SYS_DATETIME, INTERVAL '1:20' HOUR_SECOND);
06:18:24.149 PM 06/28/2010 06:19:44.149 PM 06/28/2010
SELECT ADDDATE('0000-00-00', 1 );
ERROR: Conversion error in date format.
SELECT ADDDATE('0001-01-01 00:00:00', -1);
'12:00:00.000 AM 00/00/0000'
ADDTIME¶
-
ADDTIME
(expr1, expr2)¶ The ADDTIME function adds or subtracts a value of specific time. The first argument is DATE, DATETIME, TIMESTAMP, or TIME type and the second argument is TIME, DATETIME, or TIMESTAMP type. Time should be included in the second argument, and the date of the second argument is ignored. The return type for each argument type is follows:
First Argument Type Second Argument Type Return Type Note TIME TIME, DATETIME, TIMESTAMP TIME The result value must be equal to or less than 24 hours. DATE TIME, DATETIME, TIMESTAMP DATETIME DATETIME TIME, DATETIME, TIMESTAMP DATETIME date/time string TIME, DATETIME, TIMESTAMP or time string VARCHAR The result string includes time. Parameters: - expr1 -- DATE, DATETIME, TIME or TIMESTAMP type
- expr2 -- DATETIME, TIMESTAMP, TIME type or date/time string
SELECT ADDTIME(datetime'2007-12-31 23:59:59', time'1:1:2');
01:01:01.000 AM 01/01/2008
SELECT ADDTIME(time'01:00:00', time'02:00:01');
03:00:01 AM
ADD_MONTHS¶
-
ADD_MONTHS
(date_argument, month)¶ The ADD_MONTHS function adds a month value to the expression date_argument of DATE type, and it returns a DATE type value. If the day (dd) of the value specified as an argument exists within the month of the result value of the operation, it returns the given day (dd); otherwise returns the last day of the given month (dd). If the result value of the operation exceeds the expression range of the DATE type, it returns an error.
Parameters: - date_argument -- Specifies an expression of DATE type. To specify a TIMESTAMP or DATETIME value, an explicit casting to DATE type is required. If the value is NULL, NULL is returned.
- month -- Specifies the number of the months to be added to the date_argument. Both positive and negative values can be specified. If the given value is not an integer type, conversion to an integer type by an implicit casting (rounding to the first place after the decimal point) is performed. If the value is NULL, NULL is returned.
--it returns DATE type value by adding month to the first argument
SELECT ADD_MONTHS(DATE '2008-12-25', 5), ADD_MONTHS(DATE '2008-12-25', -5);
05/25/2009 07/25/2008
SELECT ADD_MONTHS(DATE '2008-12-31', 5.5), ADD_MONTHS(DATE '2008-12-31', -5.5);
06/30/2009 06/30/2008
SELECT ADD_MONTHS(CAST (SYS_DATETIME AS DATE), 5), ADD_MONTHS(CAST (SYS_TIMESTAMP AS DATE), 5);
07/03/2010 07/03/2010
The following are examples of using timezone type values. For timezone related description, see Date/Time Types with Timezone.
SELECT ADD_MONTHS (datetimeltz'2001-10-11 10:11:12', 1);
11/11/2001
SELECT ADD_MONTHS (datetimetz'2001-10-11 10:11:12 Europe/Paris', 1);
11/11/2001
SELECT ADD_MONTHS (timestampltz'2001-10-11 10:11:12', 1);
11/11/2001
SELECT ADD_MONTHS (timestamptz'2001-10-11 10:11:12 Europe/Paris', 1);
11/11/2001
CURDATE, CURRENT_DATE¶
-
CURDATE
()¶
-
CURRENT_DATE
()¶
-
CURRENT_DATE
¶ CURDATE (), CURRENT_DATE and CURRENT_DATE () are used interchangeably and they return the current date of session as the DATE type (MM/DD/YYYY or YYYY-MM-DD). The unit is day. When the time zone of the current session is same as that of server, these functions are same as
SYS_DATE
,SYSDATE
. Please referSYS_DATE
,SYSDATE
and the following examples to find a difference andDBTIMEZONE()
,SESSIONTIMEZONE()
for details of the functions.If input every argument value of year, month, and day is 0, the return value is determined by the return_null_on_function_errors system parameter; if it is set to yes, then NULL is returned; if it is set to no, an error is returned. The default value is no.
Return type: DATE
SELECT DBTIMEZONE(), SESSIONTIMEZONE();
dbtimezone sessiontimezone
============================================
'Asia/Seoul' 'Asia/Seoul'
-- it returns the current date in DATE type
SELECT CURDATE(), CURRENT_DATE(), CURRENT_DATE, SYS_DATE, SYSDATE;
CURRENT_DATE CURRENT_DATE CURRENT_DATE SYS_DATE SYS_DATE
========================================================================
02/05/2016 02/05/2016 02/05/2016 02/05/2016 02/05/2016
-- it returns the date 60 days added to the current date
SELECT CURDATE()+60;
CURRENT_DATE +60
===================
04/05/2016
-- change session time from 'Asia/Seoul' to 'America/Los_Angeles'
SET TIME ZONE 'America/Los_Angeles';
SELECT DBTIMEZONE(), SESSIONTIMEZONE();
dbtimezone sessiontimezone
============================================
'Asia/Seoul' 'America/Los_Angeles'
-- Note that CURDATE() and SYS_DATE returns different results
SELECT CURDATE(), CURRENT_DATE(), CURRENT_DATE, SYS_DATE, SYSDATE;
CURRENT_DATE CURRENT_DATE CURRENT_DATE SYS_DATE SYS_DATE
========================================================================
02/04/2016 02/04/2016 02/04/2016 02/05/2016 02/05/2016
Warning
As 10.0, CURDATE (), CURRENT_DATE, CURRENT_DATE () are different from SYS_DATE and SYSDATE. They are synonym for 9.x and lower.
CURRENT_DATETIME, NOW¶
-
CURRENT_DATETIME
()¶
-
CURRENT_DATETIME
¶
-
NOW
()¶ CURRENT_DATETIME, CURRENT_DATETIME () and NOW () are used interchangeably, and they return the current date and time of session in DATETIME type. The unit is millisecond. When the time zone of the current session is same as that of server, these functions are same as
SYS_DATETIME
,SYSDATETIME
. Please also referSYS_DATETIME
,SYSDATETIME
to find a difference andDBTIMEZONE()
,SESSIONTIMEZONE()
for details of the functions.Return type: DATETIME
SELECT DBTIMEZONE(), SESSIONTIMEZONE();
dbtimezone sessiontimezone
============================================
'Asia/Seoul' 'Asia/Seoul'
-- it returns the current date and time in DATETIME type
SELECT NOW(), SYS_DATETIME;
CURRENT_DATETIME SYS_DATETIME
==============================================================
04:05:09.292 PM 02/05/2016 04:05:09.292 PM 02/05/2016
-- it returns the timestamp value 1 hour added to the current sys_datetime value
SELECT TO_CHAR(SYSDATETIME+3600*1000, 'YYYY-MM-DD HH24:MI');
to_char( SYS_DATETIME +3600*1000, 'YYYY-MM-DD HH24:MI')
======================
'2016-02-05 17:05'
-- change session time from 'Asia/Seoul' to 'America/Los_Angeles'
set time zone 'America/Los_Angeles';
SELECT DBTIMEZONE(), SESSIONTIMEZONE();
dbtimezone sessiontimezone
============================================
'Asia/Seoul' 'America/Los_Angeles'
-- Note that NOW() and SYS_DATETIME return different results
SELECT NOW(), SYS_DATETIME;
CURRENT_DATETIME SYS_DATETIME
==============================================================
11:08:57.041 PM 02/04/2016 04:08:57.041 PM 02/05/2016
Warning
As 10.0, CURRENT_DATETIME (), NOW () are different from SYS_DATEIME, SYSDATETIME. They are synonym for 9.x and lower.
CURTIME, CURRENT_TIME¶
-
CURTIME
()¶
-
CURRENT_TIME
¶
-
CURRENT_TIME
()¶ CURTIME (), CURRENT_TIME and CURRENT_TIME () are used interchangeably and they return the current time of session as TIME type (HH:MI:SS). The unit is second. When the time zone of the current session is same as that of server, these functions are same as
SYS_TIME
,SYSTIME
. Please also referSYS_TIME
,SYSTIME
to find a difference andDBTIMEZONE()
,SESSIONTIMEZONE()
for details of the functions.Return type: TIME
SELECT DBTIMEZONE(), SESSIONTIMEZONE();
dbtimezone sessiontimezone
============================================
'Asia/Seoul' 'Asia/Seoul'
-- it returns the current time in TIME type
SELECT CURTIME(), CURRENT_TIME(), CURRENT_TIME, SYS_TIME, SYSTIME;
CURRENT_TIME CURRENT_TIME CURRENT_TIME SYS_TIME SYS_TIME
==========================================================================
04:22:54 PM 04:22:54 PM 04:22:54 PM 04:22:54 PM 04:22:54 PM
-- change session time from 'Asia/Seoul' to 'America/Los_Angeles'
SET TIME ZONE 'AMERICA/LOS_ANGELES';
SELECT DBTIMEZONE(), SESSIONTIMEZONE();
dbtimezone sessiontimezone
============================================
'Asia/Seoul' 'America/Los_Angeles'
-- Note that CURTIME() and SYS_TIME return different results
SELECT CURTIME(), CURRENT_TIME(), CURRENT_TIME, SYS_TIME, SYSTIME;
CURRENT_TIME CURRENT_TIME CURRENT_TIME SYS_TIME SYS_TIME
==========================================================================
11:23:16 PM 11:23:16 PM 11:23:16 PM 04:23:16 PM 04:23:16 PM
Warning
As 10.0, CURTIME (), CURRENT_TIME () are different from SYS_TIME, SYSTIME. They are synonym for 9.x and lower.
CURRENT_TIMESTAMP, LOCALTIME, LOCALTIMESTAMP¶
-
CURRENT_TIMESTAMP
¶
-
CURRENT_TIMESTAMP
()¶
-
LOCALTIME
¶
-
LOCALTIME
()¶
-
LOCALTIMESTAMP
¶
-
LOCALTIMESTAMP
()¶ CURRENT_TIMESTAMP, CURRENT_TIMESTAMP (), LOCALTIME, LOCALTIME (), LOCALTIMESTAMP and LOCALTIMESTAMP () are used interchangeably and they return the current date and time of session as TIMESTAMP type. The unit is second. When the time zone of the current session is same as that of server, these functions are same as
SYS_TIMESTAMP
,SYSTIMESTAMP
. Please also referSYS_TIMESTAMP
,SYSTIMESTAMP
to find a difference andDBTIMEZONE()
,SESSIONTIMEZONE()
for details of the functions.Return type: TIMESTAMP
SELECT DBTIMEZONE(), SESSIONTIMEZONE();
dbtimezone sessiontimezone
============================================
'Asia/Seoul' 'Asia/Seoul'
-- it returns the current date and time in TIMESTAMP type of session and server timezones.
SELECT LOCALTIME, SYS_TIMESTAMP;
CURRENT_TIMESTAMP SYS_TIMESTAMP
======================================================
04:34:16 PM 02/05/2016 04:34:16 PM 02/05/2016
-- change session time from 'Asia/Seoul' to 'America/Los_Angeles'
SET TIME ZONE 'America/Los_Angeles';
SELECT DBTIMEZONE(), SESSIONTIMEZONE();
dbtimezone sessiontimezone
============================================
'Asia/Seoul' 'America/Los_Angeles'
-- Note that LOCALTIME() and SYS_TIMESTAMP return different results
SELECT LOCALTIME, SYS_TIMESTAMP;
CURRENT_TIMESTAMP SYS_TIMESTAMP
======================================================
11:34:37 PM 02/04/2016 04:34:37 PM 02/05/2016
Warning
As 10.0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP (), LOCALTIME, LOCALTIME (), LOCALTIMESTAMP and LOCALTIMESTAMP () are different from SYS_TIMESTAMP (), SYSTIMESTAMP. They are synonym for 9.x and lower.
DATE¶
-
DATE
(date)¶ The DATE function extracts the date part from specified argument, and returns it as 'MM/DD/YYYY' format string. Arguments that can be specified are DATE, TIMESTAMP and DATETIME types. The return value is a VARCHAR type.
0 is not allowed in the argument value corresponding to year, month, and day; however, if 0 is inputted in every argument value corresponding to date and time, string where 0 is specified for year, month, and day is returned.
Parameters: date -- DATE, TIMESTAMP or DATETIME can be specified. Return type: STRING
SELECT DATE('2010-02-27 15:10:23');
'02/27/2010'
SELECT DATE(NOW());
'04/01/2010'
SELECT DATE('0000-00-00 00:00:00');
'00/00/0000'
DATEDIFF¶
-
DATEDIFF
(date1, date2)¶ The DATEDIFF function returns the difference between two arguments as an integer representing the number of days. Arguments that can be specified are DATE, TIMESTAMP and DATETIME types and its return value is only INTEGER type.
If every input argument value of date and time is 0, the return value is determined by the return_null_on_function_errors system parameter; if it is set to yes, then NULL is returned; if it is set to no, an error is returned. The default value is no.
Parameters: date1,date2 -- Specifies the types that include date (DATE, TIMESTAMP or DATETIME) type or string that represents the value of corresponding type. If invalid string is specified, an error is returned. Return type: INT
SELECT DATEDIFF('2010-2-28 23:59:59','2010-03-02');
-2
SELECT DATEDIFF('0000-00-00 00:00:00', '2010-2-28 23:59:59');
ERROR: Conversion error in date format.
The following are examples of using timezone type values. For timezone related description, see Date/Time Types with Timezone.
SELECT IF(DATEDIFF('2002-03-03 12:00:00 AM','1990-01-01 11:59:59 PM') = DATEDIFF(timestampltz'2002-03-03 12:00:00 AM',timestampltz'1990-01-01 11:59:59 PM'),'ok','nok');
'ok'
DATE_SUB, SUBDATE¶
-
DATE_SUB
(date, INTERVAL expr unit)¶
-
SUBDATE
(date, INTERVAL expr unit)¶
-
SUBDATE
(date, days) The functions DATE_SUB and SUBDATE () are used interchangeably and they perform an addition or subtraction operation on a specific DATE value. The value is returned in DATE or DATETIME type. If the date resulting from the operation exceeds the last day of the month, the function returns a valid DATE value considering the last date of the month.
If every input argument value of date and time is 0, the return value is determined by the return_null_on_function_errors system parameter; if it is set to yes, then NULL is returned; if it is set to no, an error is returned. The default value is no.
If the calculated value is between '0000-00-00 00:00:00' and '0001-01-01 00:00:00', a value having 0 for all arguments is returned in DATE or DATETIME type. Note that operation in JDBC program is determined by the configuration of zeroDateTimeBehavior, connection URL property (see Configuration Connection for details).
Parameters: - date -- It is a DATE or TIMESTAMP expression that represents the start date. If an invalid DATE value such as '2006-07-00' is specified, NULL is returned.
- expr -- It represents the interval value to be subtracted from the start date. If a negative number is specified next to the INTERVAL keyword, the interval value is added to the start date.
- unit -- It represents the unit of the interval value specified in the exp expression. To check the expr argument for the unit value, see the table of
ADDDATE()
.
Return type: DATE or DATETIME
SELECT SYSDATE, SUBDATE(SYSDATE,INTERVAL 24 HOUR), SUBDATE(SYSDATE, 1);
03/30/2010 12:00:00.000 AM 03/29/2010 03/29/2010
--it adds days when argument < 0
SELECT SYSDATE, SUBDATE(SYSDATE,INTERVAL -24 HOUR), SUBDATE(SYSDATE, -1);
03/30/2010 12:00:00.000 AM 03/31/2010 03/31/2010
SELECT SUBDATE('0000-00-00 00:00:00', -50);
ERROR: Conversion error in date format.
SELECT SUBDATE('0001-01-01 00:00:00', 10);
'12:00:00.000 AM 00/00/0000'
DAY, DAYOFMONTH¶
-
DAY
(date)¶
-
DAYOFMONTH
(date)¶ The function DAY or DAYOFMONTH returns day in the range of 1 to 31 from the specified parameter. You can specify the DATE, TIMESTAMP or DATETIME type; the value is returned in INTEGER type.
0 is not allowed in the argument value corresponding to year, month, and day; however, if 0 is inputted in every argument value corresponding to year, month, and day, 0 is returned as an exception.
Parameters: date -- Date Return type: INT
SELECT DAYOFMONTH('2010-09-09');
9
SELECT DAY('2010-09-09 19:49:29');
9
SELECT DAYOFMONTH('0000-00-00 00:00:00');
0
DAYOFWEEK¶
-
DAYOFWEEK
(date)¶ The DAYOFWEEK function returns a day in the range of 1 to 7 (1: Sunday, 2: Monday, ..., 7: Saturday) from the specified parameters. The day index is same as the ODBC standards. You can specify the DATE, TIMESTAMP or DATETIME type; the value is returned in INTEGER type.
If every input argument value of year, month, and day is 0, the return value is determined by the return_null_on_function_errors system parameter; if it is set to yes, then NULL is returned; if it is set to no, an error is returned. The default value is no.
Parameters: date -- Date Return type: INT
SELECT DAYOFWEEK('2010-09-09');
5
SELECT DAYOFWEEK('2010-09-09 19:49:29');
5
SELECT DAYOFWEEK('0000-00-00');
ERROR: Conversion error in date format.
DAYOFYEAR¶
-
DAYOFYEAR
(date)¶ The DAYOFYEAR function returns the day of a year in the range of 1 to 366. You can specify the DATE, TIMESTAMP or DATETIME types; the value is returned in INTEGER type.
If every input argument value of year, month, and day is 0, the return value is determined by the return_null_on_function_errors system parameter; if it is set to yes, then NULL is returned; if it is set to no, an error is returned. The default value is no.
Parameters: date -- Date Return type: INT
SELECT DAYOFYEAR('2010-09-09');
252
SELECT DAYOFYEAR('2010-09-09 19:49:29');
252
SELECT DAYOFYEAR('0000-00-00');
ERROR: Conversion error in date format.
EXTRACT¶
-
EXTRACT
(field FROM date-time_argument)¶ The EXTRACT operator extracts the values from date-time_argument and then converts the value type into INTEGER.
0 is not allowed in the input argument value corresponding to year, month, and day; however, if 0 is inputted in every argument value corresponding to date and time, 0 is returned as an exception.
Parameters: - field -- Specifies a value to be extracted from date-time expression.
- date-time_argument -- An expression that returns a value of date-time. This expression must be one of TIME, DATE, TIMESTAMP, or DATETIME types. If the value is NULL, NULL is returned.
Return type: INT
SELECT EXTRACT(MONTH FROM DATETIME '2008-12-25 10:30:20.123' );
12
SELECT EXTRACT(HOUR FROM DATETIME '2008-12-25 10:30:20.123' );
10
SELECT EXTRACT(MILLISECOND FROM DATETIME '2008-12-25 10:30:20.123' );
123
SELECT EXTRACT(MONTH FROM '0000-00-00 00:00:00');
0
The following are examples of using timezone type values. For timezone related description, see Date/Time Types with Timezone.
SELECT EXTRACT (MONTH FROM datetimetz'10/15/1986 5:45:15.135 am Europe/Bucharest');
10
SELECT EXTRACT (MONTH FROM datetimeltz'10/15/1986 5:45:15.135 am Europe/Bucharest');
10
SELECT EXTRACT (MONTH FROM timestampltz'10/15/1986 5:45:15 am Europe/Bucharest');
10
SELECT EXTRACT (MONTH FROM timestamptz'10/15/1986 5:45:15 am Europe/Bucharest');
10
FROM_DAYS¶
-
FROM_DAYS
(N)¶ The FROM_DAYS function returns a date value in DATE type if INTEGER type is inputted as an argument.
It is not recommended to use the FROM_DAYS function for dates prior to the year 1582 because the function does not take dates prior to the introduction of the Gregorian Calendar into account.
If a value in the range of 0 to 3,652,424 can be inputted as an argument. If a value in the range of 0 to 365 is inputted, 0 is returned. 3,652,424, which is the maximum value, means the last day of year 9999.
Parameters: N -- Integer in the range of 0 to 3,652,424 Return type: DATE
SELECT FROM_DAYS(719528);
01/01/1970
SELECT FROM_DAYS('366');
01/03/0001
SELECT FROM_DAYS(3652424);
12/31/9999
SELECT FROM_DAYS(0);
00/00/0000
FROM_TZ¶
-
FROM_TZ
(datetime, timezone_string)¶ Converts date/time type without timezone as date/time type with timezone by adding timezone to DATETIME typed value. Input value's type is DATETIME, and the result value's type is DATETIMETZ.
Parameters: - datetime -- DATETIME
- timezone_string -- String representing a timezone name or and offset '+05:00', 'Asia/Seoul'.
Return type: DATETIMETZ
SELECT FROM_TZ(datetime '10/10/2014 00:00:00 AM', 'Europe/Vienna');
12:00:00.000 AM 10/10/2014 Europe/Vienna CEST
SELECT FROM_TZ(datetime '10/10/2014 23:59:59 PM', '+03:25:25');
11:59:59.000 PM 10/10/2014 +03:25:25
For timezone related description, see Date/Time Types with Timezone.
See also
FROM_UNIXTIME¶
-
FROM_UNIXTIME
(unix_timestamp[, format])¶ The FROM_UNIXTIME function returns the string of the specified format in VARCHAR type if the argument format is specified; if the argument format is omitted, it returns a value of TIMESTAMP type. Specify the arguement unix_timestamp as an INTEGER type that corresponds to the UNIX timestamp. The returned value is displayed in the current time zone.
It displays the result according to the format that you specified, and the date/time format, format follows the Date/Time Format 2 table of
DATE_FORMAT()
.The relation is not one of one-to-one correspondence between TIMESTAMP and UNIX timestamp so if you use
UNIX_TIMESTAMP()
or FROM_UNIXTIME function, partial value could be lost. For details, seeUNIX_TIMESTAMP()
.0 is not allowed in the argument value corresponding to year, month, and day; however, if 0 is inputted in every argument value corresponding to date and time, string where 0 is specified for every date and time value is returned. Note that operation in JDBC program is determined by the configuration of zeroDateTimeBehavior, connection URL property (see Configuration Connection for details).
Parameters: - unix_timestamp -- Positive integer
- format -- Time format. Follows the date/time format of the
DATE_FORMAT()
.
Return type: STRING, INT
SELECT FROM_UNIXTIME(1234567890);
01:31:30 AM 02/14/2009
SELECT FROM_UNIXTIME('1000000000');
04:46:40 AM 09/09/2001
SELECT FROM_UNIXTIME(1234567890,'%M %Y %W');
'February 2009 Saturday'
SELECT FROM_UNIXTIME('1234567890','%M %Y %W');
'February 2009 Saturday'
SELECT FROM_UNIXTIME(0);
12:00:00 AM 00/00/0000
HOUR¶
-
HOUR
(time)¶ The HOUR function extracts the hour from the specified parameter and then returns the value in integer. The type TIME, TIMESTAMP or DATETIME can be specified and a value is returned in the INTEGER type.
Parameters: time -- Time Return type: INT
SELECT HOUR('12:34:56');
12
SELECT HOUR('2010-01-01 12:34:56');
12
SELECT HOUR(datetime'2010-01-01 12:34:56');
12
LAST_DAY¶
-
LAST_DAY
(date_argument)¶ The LAST_DAY function returns the last day of the given month as DATE type.
If every input argument value of year, month, and day is 0, the return value is determined by the return_null_on_function_errors system parameter; if it is set to yes, then NULL is returned; if it is set to no, an error is returned. The default value is no.
Parameters: date_argument -- Specifies an expression of DATE type. To specify a TIMESTAMP or DATETIME value, explicit casting to DATE is required. If the value is NULL, NULL is returned. Return type: DATE
--it returns last day of the month in DATE type
SELECT LAST_DAY(DATE '1980-02-01'), LAST_DAY(DATE '2010-02-01');
02/28/1980 02/28/2010
--it returns last day of the month when explicitly casted to DATE type
SELECT LAST_DAY(CAST (SYS_TIMESTAMP AS DATE)), LAST_DAY(CAST (SYS_DATETIME AS DATE));
02/28/2010 02/28/2010
SELECT LAST_DAY('0000-00-00');
ERROR: Conversion error in date format.
MAKEDATE¶
-
MAKEDATE
(year, dayofyear)¶ The MAKEDATE function returns a date from the specified parameter. You can specify an INTEGER type corresponding to the day of the year in the range of 1 to 9999 as an argument; the value in the range of 1/1/1 to 12/31/9999 is returned in DATE type. If the day of the year has passed the corresponding year, it will become the next year. For example, MAKEDATE(1999, 366) will return 2000-01-01. However, if you input a value in the range of 0 to 69 as the year, it will be processed as the year 2000-2069, if it is a value in the range of 70 to 99, it will be processed as the year 1970-1999.
If every value specified in year and dayofyear is 0, the return value is determined by the return_null_on_function_errors system parameter; if it is set to yes, then NULL is returned; if it is set to no, an error is returned. The default value is no.
Parameters: - year -- Year in the range of 1 to 9999
- dayofyear -- If you input a value in the range of 0 to 99 in the argument, it is handled as an exception; dayofyear must be equal to or less than 3,615,902 and the return value of MAKEDATE(100, 3615902) is 9999/12/31.
Return type:
SELECT MAKEDATE(2010,277);
10/04/2010
SELECT MAKEDATE(10,277);
10/04/2010
SELECT MAKEDATE(70,277);
10/04/1970
SELECT MAKEDATE(100,3615902);
12/31/9999
SELECT MAKEDATE(9999,365);
12/31/9999
SELECT MAKEDATE(0,0);
ERROR: Conversion error in date format.
MAKETIME¶
-
MAKETIME
(hour, min, sec)¶ The MAKETIME function returns the hour from specified argument in the AM/PM format. You can specify the INTEGER types corresponding hours, minutes and seconds as arguments; the value is returned in DATETIME.
Parameters: - hour -- An integer representing the hours in the range of 0 to 23
- min -- An integer representing the minutes in the range of 0 to 59
- sec -- An integer representing the minutes in the range of 0 to 59
Return type: DATETIME
SELECT MAKETIME(13,34,4);
01:34:04 PM
SELECT MAKETIME('1','34','4');
01:34:04 AM
SELECT MAKETIME(24,0,0);
ERROR: Conversion error in time format.
MINUTE¶
-
MINUTE
(time)¶ The MINUTE function returns the minutes in the range of 0 to 59 from specified argument. You can specify the TIME , TIMESTAMP or DATETIME type; the value is returned in INTEGER type.
Parameters: time -- Time Return type: INT
SELECT MINUTE('12:34:56');
34
SELECT MINUTE('2010-01-01 12:34:56');
34
SELECT MINUTE('2010-01-01 12:34:56.7890');
34
MONTH¶
-
MONTH
(date)¶ The MONTH function returns the month in the range of 1 to 12 from specified argument. You can specify the DATE, TIMESTAMP or DATETIME type; the value is returned in INTEGER type.
0 is not allowed in the argument value corresponding to year, month, and day; however, if 0 is inputted in every argument value corresponding to date, 0 is returned as an exception.
Parameters: date -- Date Return type: INT
SELECT MONTH('2010-01-02');
1
SELECT MONTH('2010-01-02 12:34:56');
1
SELECT MONTH('2010-01-02 12:34:56.7890');
1
SELECT MONTH('0000-00-00');
0
MONTHS_BETWEEN¶
-
MONTHS_BETWEEN
(date_argument, date_argument)¶ The MONTHS_BETWEEN function returns the difference between the given DATE value. The return value is DOUBLE type. An integer value is returned if the two dates specified as arguments are identical or are the last day of the given month; otherwise, a value obtained by dividing the day difference by 31 is returned.
Parameters: date_argument -- Specifies an expression of DATE type. TIMESTAMP or DATETIME value also can be used. If the value is NULL, NULL is returned. Return type: DOUBLE
--it returns the negative months when the first argument is the previous date
SELECT MONTHS_BETWEEN(DATE '2008-12-31', DATE '2010-6-30');
-1.800000000000000e+001
--it returns integer values when each date is the last date of the month
SELECT MONTHS_BETWEEN(DATE '2010-6-30', DATE '2008-12-31');
1.800000000000000e+001
--it returns months between two arguments when explicitly casted to DATE type
SELECT MONTHS_BETWEEN(CAST (SYS_TIMESTAMP AS DATE), DATE '2008-12-25');
1.332258064516129e+001
--it returns months between two arguments when explicitly casted to DATE type
SELECT MONTHS_BETWEEN(CAST (SYS_DATETIME AS DATE), DATE '2008-12-25');
1.332258064516129e+001
The following are examples of using timezone type values. For timezone related description, see Date/Time Types with Timezone.
SELECT MONTHS_BETWEEN(datetimetz'2001-10-11 10:11:12 +02:00', datetimetz'2001-05-11 10:11:12 +03:00');
5.000000000000000e+00
NEW_TIME¶
-
NEW_TIME
(src_datetime, src_timezone, dst_timezone)¶ Moves a date value from a timezone to the other timezone. The type of src_datetime is DATETIME or TIME, and the return value's type is the same with the src_datetime's value.
Parameters: - src_datetime -- input value of DATETIME or TIME
- src_timezone -- a region name of a source timezone
- dst_timezion -- a region name of a target timezone
Return type: the same type with a src_datetimes type
SELECT NEW_TIME(datetime '10/10/2014 10:10:10 AM', 'Europe/Vienna', 'Europe/Bucharest');
11:10:10.000 AM 10/10/2014
TIME type only accept an offset timezone as an input argument; a region name is not allowed.
SELECT NEW_TIME(time '10:10:10 PM', '+03:00', '+10:00');
05:10:10 AM
To see the timezone related description, see Date/Time Types with Timezone.
See also
QUARTER¶
-
QUARTER
(date)¶ The QUARTER function returns the quarter in the range of 1 to 4 from specified argument. You can specify the DATE, TIMESTAMP or DATETIME type; the value is returned in INTEGER type.
Parameters: date -- Date Return type: INT
SELECT QUARTER('2010-05-05');
2
SELECT QUARTER('2010-05-05 12:34:56');
2
SELECT QUARTER('2010-05-05 12:34:56.7890');
2
The following are examples of using timezone type values. For timezone related description, see Date/Time Types with Timezone.
SELECT QUARTER('2008-04-01 01:02:03 Asia/Seoul');
2
SELECT QUARTER(datetimetz'2003-12-31 01:02:03.1234 Europe/Paris');
4
ROUND¶
-
ROUND
(date, fmt)¶ This function rounds date to the unit specified by the format string, fmt. It returns a value of DATE type.
Parameters: - date -- The value of DATE, TIMESTAMP or DATETIME
- fmt -- Specifies the format for the truncating unit. If omitted, "dd" is default.
Return type: The format and its unit and the return value are as follows:
Format Unit Return value 'yyyy' or 'yy' year a value rounded to year 'mm' or 'month' month a value rounded to month 'q' quarter a value rounded to quarter, one of 1/1, 4/1, 7/1, 10/1 'day' week a value rounded to week, this Sunday of date week or the next Sunday of date week 'dd' day a value rounded to day 'hh' hour a value rounded to hour 'mi' minute a value rounded to minute 'ss' second a value rounded to second
SELECT ROUND(date'2012-10-26', 'yyyy');
01/01/2013
SELECT ROUND(timestamp'2012-10-26 12:10:10', 'mm');
11/01/2012
SELECT ROUND(datetime'2012-12-26 12:10:10', 'dd');
12/27/2012
SELECT ROUND(datetime'2012-12-26 12:10:10', 'day');
12/30/2012
SELECT ROUND(datetime'2012-08-26 12:10:10', 'q');
10/01/2012
SELECT TRUNC(datetime'2012-08-26 12:10:10', 'q');
07/01/2012
SELECT ROUND(datetime'2012-02-28 23:10:00', 'hh');
02/28/2012
SELECT ROUND(datetime'2012-02-28 23:58:59', 'hh');
02/29/2012
SELECT ROUND(datetime'2012-02-28 23:59:59', 'mi');
02/29/2012
SELECT ROUND(datetime'2012-02-28 23:59:59.500', 'ss');
02/29/2012
In order to truncate date instead of rounding, please see TRUNC(date, fmt).
SEC_TO_TIME¶
-
SEC_TO_TIME
(second)¶ The SEC_TO_TIME function returns the time including hours, minutes and seconds from specified argument. You can specify the INTEGER type in the range of 0 to 86,399; the value is returned in TIME type.
Parameters: second -- Seconds in the range of 0 to 86,399 Return type: TIME
SELECT SEC_TO_TIME(82800);
sec_to_time(82800)
=====================
11:00:00 PM
SELECT SEC_TO_TIME('82800.3');
sec_to_time('82800.3')
=========================
11:00:00 PM
SELECT SEC_TO_TIME(86399);
sec_to_time(86399)
=====================
11:59:59 PM
SECOND¶
-
SECOND
(time)¶ The SECOND function returns the seconds in the range of 0 to 59 from specified argument. You can specify the TIME, TIMESTAMP or DATETIME; the value is returned in INTEGER type.
Parameters: time -- Time Return type: INT
SELECT SECOND('12:34:56');
second('12:34:56')
=====================
56
SELECT SECOND('2010-01-01 12:34:56');
second('2010-01-01 12:34:56')
================================
56
SELECT SECOND('2010-01-01 12:34:56.7890');
second('2010-01-01 12:34:56.7890')
=====================================
56
SYS_DATE, SYSDATE¶
-
SYS_DATE
¶
-
SYSDATE
¶ SYS_DATE and SYSDATE are used interchangeably and they return the current date of server as the DATE type (MM/DD/YYYY or YYYY-MM-DD). The unit is day. When the time zone of the current session is same as that of server, these functions are same as
CURDATE()
,CURRENT_DATE()
andCURRENT_DATE
. Please also referCURDATE()
,CURRENT_DATE()
to find a difference andDBTIMEZONE()
,SESSIONTIMEZONE()
for details of the functions.If input every argument value of year, month, and day is 0, the return value is determined by the return_null_on_function_errors system parameter; if it is set to yes, then NULL is returned; if it is set to no, an error is returned. The default value is no.
Return type: DATE
SELECT DBTIMEZONE(), SESSIONTIMEZONE();
dbtimezone sessiontimezone
============================================
'Asia/Seoul' 'Asia/Seoul'
-- it returns the current date in DATE type
SELECT CURDATE(), CURRENT_DATE(), CURRENT_DATE, SYS_DATE, SYSDATE;
CURRENT_DATE CURRENT_DATE CURRENT_DATE SYS_DATE SYS_DATE
========================================================================
02/05/2016 02/05/2016 02/05/2016 02/05/2016 02/05/2016
-- it returns the date 60 days added to the current date
SELECT CURDATE()+60;
CURRENT_DATE +60
===================
04/05/2016
-- change session time from 'Asia/Seoul' to 'America/Los_Angeles'
SET TIME ZONE 'America/Los_Angeles';
SELECT DBTIMEZONE(), SESSIONTIMEZONE();
dbtimezone sessiontimezone
============================================
'Asia/Seoul' 'America/Los_Angeles'
-- Note that CURDATE() and SYS_DATE returns different results
SELECT CURDATE(), CURRENT_DATE(), CURRENT_DATE, SYS_DATE, SYSDATE;
CURRENT_DATE CURRENT_DATE CURRENT_DATE SYS_DATE SYS_DATE
========================================================================
02/04/2016 02/04/2016 02/04/2016 02/05/2016 02/05/2016
Warning
As 10.0, SYS_DATE and SYSDATE are different from CURDATE (), CURRENT_DATE, CURRENT_DATE (). They are synonym for 9.x and lower.
SYS_DATETIME, SYSDATETIME¶
-
SYS_DATETIME
¶
-
SYSDATETIME
¶ SYS_DATETIME and SYSDATETIME are used interchangeably, and they return the current date and time of server in DATETIME type. The unit is millisecond. When the time zone of the current session is same as that of server, these functions are same as
CURRENT_DATETIME()
,CURRENT_DATETIME
,NOW()
. Please also referCURRENT_DATETIME()
,NOW()
to find a difference andDBTIMEZONE()
,SESSIONTIMEZONE()
for details of the functions.Return type: DATETIME
SELECT DBTIMEZONE(), SESSIONTIMEZONE();
dbtimezone sessiontimezone
============================================
'Asia/Seoul' 'Asia/Seoul'
-- it returns the current date and time in DATETIME type
SELECT NOW(), SYS_DATETIME;
CURRENT_DATETIME SYS_DATETIME
==============================================================
04:05:09.292 PM 02/05/2016 04:05:09.292 PM 02/05/2016
-- it returns the timestamp value 1 hour added to the current sys_datetime value
SELECT TO_CHAR(SYSDATETIME+3600*1000, 'YYYY-MM-DD HH24:MI');
to_char( SYS_DATETIME +3600*1000, 'YYYY-MM-DD HH24:MI')
======================
'2016-02-05 17:05'
-- change session time from 'Asia/Seoul' to 'America/Los_Angeles'
SET TIME ZONE 'America/Los_Angeles';
SELECT DBTIMEZONE(), SESSIONTIMEZONE();
dbtimezone sessiontimezone
============================================
'Asia/Seoul' 'America/Los_Angeles'
-- Note that NOW() and SYS_DATETIME return different results
SELECT NOW(), SYS_DATETIME;
CURRENT_DATETIME SYS_DATETIME
==============================================================
11:08:57.041 PM 02/04/2016 04:08:57.041 PM 02/05/2016
Warning
As 10.0, SYS_DATEIME, SYSDATETIME are different from CURRENT_DATETIME (), NOW (). They are synonym for 9.x and lower.
SYS_TIME, SYSTIME¶
-
SYS_TIME
¶
-
SYSTIME
¶ SYS_TIME and SYSTIME are used interchangeably and they return the current time of server as TIME type (HH:MI:SS). The unit is second. When the time zone of the current session is same as that of server, these functions are same as
CURTIME()
,CURRENT_TIME
,CURRENT_TIME()
. Please also referCURTIME()
,CURRENT_TIME()
to find a difference andDBTIMEZONE()
,SESSIONTIMEZONE()
for details of the functions.Return type: TIME
select dbtimezone(), sessiontimezone();
dbtimezone sessiontimezone
============================================
'Asia/Seoul' 'Asia/Seoul'
-- it returns the current time in TIME type
SELECT CURTIME(), CURRENT_TIME(), CURRENT_TIME, SYS_TIME, SYSTIME;
CURRENT_TIME CURRENT_TIME CURRENT_TIME SYS_TIME SYS_TIME
==========================================================================
04:22:54 PM 04:22:54 PM 04:22:54 PM 04:22:54 PM 04:22:54 PM
-- change session time from 'Asia/Seoul' to 'America/Los_Angeles'
SET TIME ZONE 'America/Los_Angeles';
select dbtimezone(), sessiontimezone();
dbtimezone sessiontimezone
============================================
'Asia/Seoul' 'America/Los_Angeles'
-- Note that CURTIME() and SYS_TIME return different results
SELECT CURTIME(), CURRENT_TIME(), CURRENT_TIME, SYS_TIME, SYSTIME;
CURRENT_TIME CURRENT_TIME CURRENT_TIME SYS_TIME SYS_TIME
==========================================================================
11:23:16 PM 11:23:16 PM 11:23:16 PM 04:23:16 PM 04:23:16 PM
Warning
As 10.0, SYS_TIME, SYSTIME are different from CURTIME (), CURRENT_TIME (). They are synonym for 9.x and lower.
SYS_TIMESTAMP, SYSTIMESTAMP¶
-
SYS_TIMESTAMP
¶
-
SYSTIMESTAMP
¶ SYS_TIMESTAMP and SYSTIMESTAMP are used interchangeably and they return the current date and time of server as TIMESTAMP type. The unit is second. When the time zone of the current session is same as that of server, these functions are same as
CURRENT_TIMESTAMP
,CURRENT_TIMESTAMP()
,LOCALTIME
,LOCALTIME()
,LOCALTIMESTAMP
,LOCALTIMESTAMP()
. Please also referCURRENT_TIMESTAMP
,CURRENT_TIMESTAMP()
,LOCALTIME
,LOCALTIME()
,LOCALTIMESTAMP
,LOCALTIMESTAMP()
to find a difference andDBTIMEZONE()
,SESSIONTIMEZONE()
for details of the functions.Return type: TIMESTAMP
SELECT DBTIMEZONE(), SESSIONTIMEZONE();
dbtimezone sessiontimezone
============================================
'Asia/Seoul' 'Asia/Seoul'
-- it returns the current date and time in TIMESTAMP type of session and server timezones.
SELECT LOCALTIME, SYS_TIMESTAMP;
CURRENT_TIMESTAMP SYS_TIMESTAMP
======================================================
04:34:16 PM 02/05/2016 04:34:16 PM 02/05/2016
-- change session time from 'Asia/Seoul' to 'America/Los_Angeles'
SET TIME ZONE 'America/Los_Angeles';
SELECT DBTIMEZONE(), SESSIONTIMEZONE();
dbtimezone sessiontimezone
============================================
'Asia/Seoul' 'America/Los_Angeles'
-- Note that LOCALTIME() and SYS_TIMESTAMP return different results
SELECT LOCALTIME, SYS_TIMESTAMP;
CURRENT_TIMESTAMP SYS_TIMESTAMP
======================================================
11:34:37 PM 02/04/2016 04:34:37 PM 02/05/2016
Warning
As 10.0, SYS_TIMESTAMP (), SYSTIMESTAMP are different from CURRENT_TIMESTAMP, CURRENT_TIMESTAMP (), LOCALTIME, LOCALTIME (), LOCALTIMESTAMP and LOCALTIMESTAMP (). They are synonym for 9.x and lower.
TIME¶
-
TIME
(time)¶ The TIME function extracts the time part from specified argument and returns the VARCHAR type string in the 'HH:MI:SS' format. You can specify the TIME, TIMESTAMP and DATETIME types.
Parameters: time -- Time Return type: STRING
SELECT TIME('12:34:56');
time('12:34:56')
======================
'12:34:56'
SELECT TIME('2010-01-01 12:34:56');
time('2010-01-01 12:34:56')
======================
'12:34:56'
SELECT TIME(datetime'2010-01-01 12:34:56');
time(datetime '2010-01-01 12:34:56')
======================
'12:34:56'
The following are examples of using timezone type values. For timezone related description, see Date/Time Types with Timezone.
SELECT TIME(datetimetz'1996-02-03 02:03:04 AM America/Lima PET');
'02:03:04'
SELECT TIME(datetimeltz'1996-02-03 02:03:04 AM America/Lima PET');
'16:03:04'
SELECT TIME(datetimeltz'2000-12-31 17:34:23.1234 -05:00');
'07:34:23.123'
SELECT TIME(datetimetz'2000-12-31 17:34:23.1234 -05:00');
'17:34:23.123'
TIME_TO_SEC¶
-
TIME_TO_SEC
(time)¶ The TIME_TO_SEC function returns the seconds in the range of 0 to 86,399 from specified argument. You can specify the TIME, TIMESTAMP or DATETIME type; the value is returned in INTEGER type.
Parameters: time -- Time Return type: INT
SELECT TIME_TO_SEC('23:00:00');
82800
SELECT TIME_TO_SEC('2010-10-04 23:00:00');
82800
SELECT TIME_TO_SEC('2010-10-04 23:00:00.1234');
82800
The following are examples of using timezone type values. For timezone related description, see Date/Time Types with Timezone.
SELECT TIME_TO_SEC(datetimeltz'1996-02-03 02:03:04 AM America/Lima PET');
57784
SELECT TIME_TO_SEC(datetimetz'1996-02-03 02:03:04 AM America/Lima PET');
7384
TIMEDIFF¶
-
TIMEDIFF
(expr1, expr2)¶ The TIMEDIFF function returns the time difference between the two specified time arguments. You can enter a date/time type, the TIME, DATE, TIMESTAMP or DATETIME type and the data types of the two arguments must be identical. The TIME will be returned and the time difference between the two arguments must be in the range of 00:00:00 -23:59:59. If it exceeds the range, an error will be returned.
Parameters: expr2 (expr1,) -- Time. The data types of the two arguments must be identical. Return type: TIME
SELECT TIMEDIFF(time '17:18:19', time '12:05:52');
05:12:27 AM
SELECT TIMEDIFF('17:18:19','12:05:52');
05:12:27 AM
SELECT TIMEDIFF('2010-01-01 06:53:45', '2010-01-01 03:04:05');
03:49:40 AM
The following are examples of using timezone type values. For timezone related description, see Date/Time Types with Timezone.
SELECT TIMEDIFF (datetimeltz'2013-10-28 03:11:12 AM Asia/Seoul', datetimeltz'2013-10-27 04:11:12 AM Asia/Seoul');
11:00:00 PM
TIMESTAMP¶
-
TIMESTAMP
(date[, time])¶ The TIMESTAMP function converts a DATE or TIMESTAMP type expression to DATETIME type.
If the DATE format string ('YYYY-MM-DD' or 'MM/DD/YYYY') or TIMESTAMP format string ('YYYY-MM-DD HH:MI:SS' or 'HH:MI:SS MM/DD/ YYYY') is specified as the first argument, the function returns it as DATETIME.
If the TIME format string ('HH:MI:SS.*FF*') is specified as the second, the function adds it to the first argument and returns the result as a DATETIME type. If the second argument is not specified, 12:00:00.000 AM is specified by default.
Parameters: - date -- The format strings can be specified as follows: 'YYYY-MM-DD', 'MM/DD/YYYY', 'YYYY-MM-DD HH:MI:SS.*FF*', 'HH:MI:SS.*FF* MM/DD/YYYY'.
- time -- The format string can be specified as follows: 'HH:MI:SS*[.*FF]'.
Return type: DATETIME
SELECT TIMESTAMP('2009-12-31'), TIMESTAMP('2009-12-31','12:00:00');
12:00:00.000 AM 12/31/2009 12:00:00.000 PM 12/31/2009
SELECT TIMESTAMP('2010-12-31 12:00:00','12:00:00');
12:00:00.000 AM 01/01/2011
SELECT TIMESTAMP('13:10:30 12/25/2008');
01:10:30.000 PM 12/25/2008
The following are examples of using timezone type values. For timezone related description, see Date/Time Types with Timezone.
SELECT TIMESTAMP(datetimetz'2010-12-31 12:00:00 America/New_York', '03:00');
03:00:00.000 PM 12/31/2010
SELECT TIMESTAMP(datetimeltz'2010-12-31 12:00:00 America/New_York', '03:00');
05:00:00.000 AM 01/01/2011
TO_DAYS¶
-
TO_DAYS
(date)¶ The TO_DAYS function returns the number of days after year 0 in the rage of 366 to 3652424 from specified argument. You can specify DATE type; the value is returned in INTEGER type.
It is not recommended to use the TO_DAYS function for dates prior to the year 1582, as the function does not take dates prior to the introduction of the Gregorian Calendar into account.
Parameters: date -- Date Return type: INT
SELECT TO_DAYS('2010-10-04');
to_days('2010-10-04')
========================
734414
SELECT TO_DAYS('2010-10-04 12:34:56');
to_days('2010-10-04 12:34:56')
================================
734414
SELECT TO_DAYS('2010-10-04 12:34:56.7890');
to_days('2010-10-04 12:34:56.7890')
======================================
734414
SELECT TO_DAYS('1-1-1');
to_days('1-1-1')
===================
366
SELECT TO_DAYS('9999-12-31');
to_days('9999-12-31')
========================
3652424
TRUNC¶
-
TRUNC
(date[, fmt])¶ This function truncates date to the unit specified by the format string, fmt. It returns a value of DATE type.
Parameters: - date -- The value of DATE, TIMESTAMP or DATETIME
- fmt -- Specifies the format for the truncating unit. If omitted, "dd" is default.
Return type: The format and its unit and the return value are as follows:
Format Unit Return value 'yyyy' or 'yy' year the same year with Jan. 1st 'mm' or 'month' month the same month with 1st 'q' quarter the same quarter with one of Jan. 1st, Apr. 1st, Jul. 1st, Oct. 1st 'day' week Sunday of the same week(starting date of the week including date) 'dd' day the same date with date
SELECT TRUNC(date'2012-12-26', 'yyyy');
01/01/2012
SELECT TRUNC(timestamp'2012-12-26 12:10:10', 'mm');
12/01/2012
SELECT TRUNC(datetime'2012-12-26 12:10:10', 'q');
10/01/2012
SELECT TRUNC(datetime'2012-12-26 12:10:10', 'dd');
12/26/2012
// It returns the date of Sunday of the week which includes date'2012-12-26'
SELECT TRUNC(datetime'2012-12-26 12:10:10', 'day');
12/23/2012
In order to round date instead of truncation, please see ROUND(date, fmt).
TZ_OFFSET¶
-
TZ_OFFSET
(timezone_string)¶ This returns a timezone offset from a timezone offset or timezone region name (e.g. '-05:00', or 'Europe/Vienna').
Parameters: timezone_string -- timezone offset of timezone region name. Return type: STRING
SELECT TZ_OFFSET('+05:00');
'+05:00'
SELECT TZ_OFFSET('Asia/Seoul');
'+09:00'
For timezone related description, see Date/Time Types with Timezone.
See also
UNIX_TIMESTAMP¶
-
UNIX_TIMESTAMP
([date])¶ The argument of the UNIX_TIMESTAMP function can be omitted. If it is omitted, the function returns the interval between '1970-01-01 00:00:00' UTC and the current system date/time in seconds as INTEGER type. If the date argument is specified, the function returns the interval between '1970-01-01 00:00:00' UTC and the specified date/time in seconds.
0 is not allowed in the argument value corresponding to year, month, and day; however, if 0 is given in every argument value corresponding to date and time, 0 is returned as an exception.
Argument of DATETIME type is considered in session timezone.
Parameters: date -- DATE type, TIMESTAMP type, TIMESTAMPTZ type, TIMESTAMPLTZ type, DATETIME type, DATETIMETZ type, DATETIMELTZ type, DATE format string ('YYYY-MM-DD' or 'MM/DD/YYYY'), TIMESTAMP format string ('YYYY-MM-DD HH:MI:SS', 'HH:MI:SS MM/DD/YYYY') or 'YYYYMMDD' format string can be specified. Return type: INT
SELECT UNIX_TIMESTAMP('1970-01-02'), UNIX_TIMESTAMP();
unix_timestamp('1970-01-02') unix_timestamp()
==================================================
54000 1270196737
SELECT UNIX_TIMESTAMP ('0000-00-00 00:00:00');
unix_timestamp('0000-00-00 00:00:00')
========================================
0
-- when used without argument, it returns the exact value at the moment of execution of each occurence
SELECT UNIX_TIMESTAMP(), SLEEP(1), UNIX_TIMESTAMP();
unix_timestamp() sleep(1) unix_timestamp()
===================================================
1454661297 0 1454661298
UTC_DATE¶
-
UTC_DATE
()¶ The UTC_DATE function returns the UTC date in 'YYYY-MM-DD' format.
Return type: STRING
SELECT UTC_DATE();
utc_date()
==============
01/12/2011
UTC_TIME¶
-
UTC_TIME
()¶ The UTC_TIME function returns the UTC time in 'HH:MI:SS' format.
Return type: STRING
SELECT UTC_TIME();
utc_time()
==============
10:35:52 AM
WEEK¶
-
WEEK
(date[, mode])¶ The WEEK function returns the week in the range of 0 to 53 from specified argument. You can specify the DATE, TIMESTAMP or DATETIME type; the value is returned in INTEGER type.
Parameters: - date -- Date
- mode -- Value in the range of 0 to 7
Return type: INT
You can omit the second argument, mode and must input a value in the range of 0 to 7. You can set that a week starts from Sunday or Monday and the range of the return value is from 0 to 53 or 1 to 53 with this value. If you omit the mode, the system parameter, default_week_format value(default: 0) will be used. The mode value means as follows:
mode | Start Day of the Week | Range | The First Week of the Year |
---|---|---|---|
0 | Sunday | 0~53 | The first week that Sunday is included in the year |
1 | Monday | 0~53 | The first week that more than three days are included in the year |
2 | Sunday | 1~53 | The first week in the year that includes a Sunday |
3 | Monday | 1~53 | The first week in the year that includes more than three days |
4 | Sunday | 0~53 | The first week in the year that includes more than three days |
5 | Monday | 0~53 | The first week in the year that includes Monday |
6 | Sunday | 1~53 | The first week in the year that includes more than three days |
7 | Monday | 1~53 | The first week in the year that includes Monday |
If the mode value is one of 0, 1, 4 or 5, and the date corresponds to the last week of the previous year, the WEEK function will return 0. The purpose is to see what nth of the year the week is so it returns 0 for the 52th week of the year 1999.
SELECT YEAR('2000-01-01'), WEEK('2000-01-01',0);
year('2000-01-01') week('2000-01-01', 0)
=============================================
2000 0
To see what n-th the week is based on the year including the start day of the week, use 0, 2, 5 or 7 as the mode value.
SELECT WEEK('2000-01-01',2);
week('2000-01-01', 2)
========================
52
SELECT WEEK('2010-04-05');
week('2010-04-05', 0)
========================
14
SELECT WEEK('2010-04-05 12:34:56',2);
week('2010-04-05 12:34:56',2)
===============================
14
SELECT WEEK('2010-04-05 12:34:56.7890',4);
week('2010-04-05 12:34:56.7890',4)
====================================
14
WEEKDAY¶
-
WEEKDAY
(date)¶ The WEEKDAY function returns the day of week in the range of 0 to 6 (0: Monday, 1: Tuesday, ..., 6: Sunday) from the specified parameter. You can specify DATE, TIMESTAMP, DATETIME types as parameters and an INTEGER type will be returned.
Parameters: date -- Date Return type: INT
SELECT WEEKDAY('2010-09-09');
weekday('2010-09-09')
========================
3
SELECT WEEKDAY('2010-09-09 13:16:00');
weekday('2010-09-09 13:16:00')
=================================
3
YEAR¶
-
YEAR
(date)¶ The YEAR function returns the year in the range of 1 to 9,999 from the specified parameter. You can specify DATE, TIMESTAMP or DATETIME type; the value is returned in INTEGER type.
Parameters: date -- Date Return type: INT
SELECT YEAR('2010-10-04');
year('2010-10-04')
=====================
2010
SELECT YEAR('2010-10-04 12:34:56');
year('2010-10-04 12:34:56')
==============================
2010
SELECT YEAR('2010-10-04 12:34:56.7890');
year('2010-10-04 12:34:56.7890')
===================================
2010